<?php

class BTreeTable extends BEntity {

    const All_Owner = 'all_owner';

    protected $tableNode;
    protected $aggerateTable;
    protected $treeItemClass;
    protected $treeItem;
    protected $owner;
    protected $ownerType;
    protected $ownerId;
    protected $tableName;

    public function __construct($treeItem, $owner = NULL, $tableName = NULL) {
        if ($treeItem instanceof BTreeItem) {
            $this->treeItemClass = get_class($treeItem);
            $this->treeItem = $treeItem;
        }

        if (is_string($treeItem)) {
            $this->treeItemClass = $treeItem;
            $this->treeItem = new $treeItem();
        }

        if ($owner instanceof BEntity) {
            $this->owner = $owner;
            $this->ownerType = get_class($owner);
            if ($owner->getId() != NULL)
                $this->ownerId = $owner->getId();

            if ($owner instanceof BSpace && $owner->getId() != NULL) {
                $this->treeItem->spaceId = $owner->getId();
            }

            if ($owner instanceof BApp && $owner->getId() != NULL) {
                $this->treeItem->appId = $owner->getId();
            }
            //$this->treeItem->setOwner($owner);
        }

        if ($tableName != NULL) {
            $this->tableName = $tableName;
        } else {
            $this->tableName = 'main';
        }
    }

    /**
     * 
     * @param array $list, keyValue list, key as column
     * value as data to show, each list as one row
     */
    public function addList($list = array(), $listIndex = NULL, $owner = NULL) {

        $tableNode = $this->getTableNode();

        if ($list instanceof BTreeList) {
            $listNode = $list->getListNode();
            if ($listNode instanceof BTreeItem) {
                $listNode->parentId = $tableNode->getId();
                //if ($list->getId() == NULL)
                if ($listIndex != NULL) {
                    $listNode->name = $listIndex;
                    $tableFullName = $tableNode->getFullName();
                    $listNode->fullName = $tableFullName . '.' . $listIndex;
                }
                $listNode->save();
                $list->setListNode($listNode);
                return $list;
            }
        }

        $listId = NULL;
        $listNode = NULL;

        if ($listIndex == NULL) {
            $listId = 'i_' . time();
            if (Blyn::app()->getActiveSpace() instanceof BSpace) {
                $listId .= '_' . Blyn::app()->getActiveSpace()->getId();
            }
            if (Blyn::app()->getCurrentUser() instanceof BUser) {
                $listId .= '_' . Blyn::app()->getCurrentUser()->getId();
            }
            //$spaceId = Blyn::app()->getActiveSpace()->getId();
            //$userId = Blyn::app()->getCurrentUser()->getId();
            //$listId .= 'i' . $spaceId . $userId . time();
        } else {
            $listId = $listIndex;
            //if exist list, update for it
            $listNode = $tableNode->getChild($listId);
        }

        if (!$listNode instanceof BTreeItem) {
            $listItem = new BTreeItem();

            $listData = array();
            if ($this->ownerType != NULL) {
                $listData['ownerType'] = $this->ownerType;
                $listItem->ownerType = $this->ownerType;
            }
            if ($this->ownerId != NULL) {
                $listData['ownerId'] = $this->ownerId;
                $listItem->ownerId = $this->ownerId;
            }

            if ($owner instanceof BEntity) {
                $listData['ownerType'] = get_class($owner);
                $listItem->ownerType = get_class($owner);

                if ($owner->getId() != NULL) {
                    $listData['ownerId'] = $owner->getId();
                    $listItem->ownerId = $owner->getId();
                }
            }

            $listData['name'] = $listId;
            $listItem->name = $listId;
            $listItem->itemType = 'TreeList';
            $listData['itemType'] = 'TreeList';

            $listNode = $tableNode->addChild($listItem);
        }

        if ($listIndex == NULL && $listNode instanceof BTreeItem) {
            //repopulate name for list
            $listNode->name = 'list' . $listNode->getId();
            $listNode->fullName = $tableNode->getFullName() . '.' . $listNode->name;
            $listNode->save();
        }

        $oList = NULL;
        if ($listNode instanceof BTreeItem) {
            $oList = new BTreeList($listNode);
        }

        if (is_array($list) && $list != NULL && $oList != NULL) {
            //$oList->addItems($list);
            $insetSql = $oList->populateInsertSqlFromArray($list);
            $listNode->getRepository()->executeSql($insetSql);
            //return $olist;
        }
        /**
          if (is_array($itemData) && $itemData != NULL) {
          $insetSql = $this->populateInsertSqlFromArray($itemData);
          $this->getListNode()->getRepository()->executeSql($insetSql);
          }
         * 
         */
        if ($oList instanceof BTreeList) {
            return $oList;
        }

        /*
          if (is_array($list)) {
          //$listItem = new BTreeItem();
          foreach ($list as $name => $value) {
          //$listItem->name = $name;
          //$listItem->value = $value;
          //$listItem->itemType = 'TreeItem';
          //$listNode->addChild($listItem);
          $listNode->addChild(array('name' => $name, 'value' => $value, 'itemType' => 'TreeItem'));
          }
          }

          if ($listNode instanceof BTreeItem) {
          $list = new BTreeList($listNode);
          return $list;
          }
         * 
         */

        return FALSE;
    }

    /**
     * this function use to add aggerate row, such as 
     * summary column for cart
     * @param array $data, keyValue, key as name of aggerate
     * name. key as column or expression of column for aggerate 
     * value
     */
    public function addAggerateList($list = null, $listIndex = NULL) {

        $aggerateTableNode = $this->getAggerateTableNode();

        if ($list instanceof BTreeList) {
            $listNode = $list->getListNode();
            $listNode->parentId = $aggerateTableNode->getId();
            $listNode->save();
            $list->setListNode($listNode);
            return $list;
        }

        $listName = NULL;
        $aggerateListNode = NULL;
        if (is_string($listIndex) && $listIndex != NULL) {
            $listName = $listIndex;
            $aggerateListNode = $aggerateTableNode->addChild($listName);
        } else {
            $listName = 'ai' . Blyn::app()->getActiveSpace()->getId() . Blyn::app()->getCurrentUser()->getId() . time();
            $aggerateListNode = $aggerateTableNode->addChild($listName);
            $aggerateListNode->name = 'aList' . $aggerateListNode->getId();
            $aggerateListNode->save();
        }

        if (is_array($list)) {
            foreach ($list as $name => $value) {
                $aggerateListNode->addChild(array('name' => $name, 'value' => $value));
            }
        }

        if ($aggerateListNode instanceof BTreeItem) {
            $list = new BTreeList();
            $list->setListNode($aggerateListNode);
            return $list;
        }

        return FALSE;
    }

    /**
     * this function use to get view data
     * 
     * @param array $condition, data for find view, format like:
     * array('itemData'=>array('itemName'=>'itemValue,....))
     * function will generate search sql by item data and other specify column
     */
    public function getView($condition = array(), $isLimited = TRUE) {

        $tableNode = $this->getTableNode();

        if (is_array($condition)) {
            $condition['parentId'] = $tableNode->getId();
        }

        if ($this->ownerType != NULL) {
            $condition['ownerType'] = $this->ownerType;
        }

        if ($this->ownerId != NULL) {
            $condition['ownerId'] = $this->ownerId;
        }

        $sql = $this->populateFindListSql($condition, $isLimited);

        $view = $tableNode->getRepository()->getView(array('sql' => $sql));

        $finalList = array();

        if ($view instanceof BView) {
            $rows = $view->rows;
            foreach ($rows as $row) {
                if ($row->attributes['item_type'] == 'TreeItem') {
                    $parentId = $row->attributes['parent_id'];
                    $list = $this->getListById($parentId);
                    if (isset($row->attributes['count'])) {
                        $list->groupCount = $row->attributes['count'];
                    }
                    $finalList[$parentId] = $list;
                }

                if ($row->attributes['item_type'] == 'TreeList') {
                    $listId = $row->getId();
                    $list = $this->getListById($listId);
                    $list->setRow($row);
                    if (isset($row->attributes['count'])) {
                        $list->groupCount = $row->attributes['count'];
                    }
                    $finalList[$listId] = $list;
                }
            }
        }

        return $finalList;
    }

    public function getViewByOwner($owner, $isLimited = TRUE) {

        if ($owner instanceof BEntity) {

            $tableNode = $this->getTableNode();
            $condition = array();

            if (is_array($condition)) {
                $condition['parentId'] = $tableNode->getId();
            }

            $condition['ownerType'] = get_class($owner);

            if ($owner->getId() != NULL) {
                $condition['ownerId'] = $owner->getId();
            }

            $sql = $this->populateFindListSql($condition, $isLimited);

            $view = $tableNode->getRepository()->getView(array('sql' => $sql));

            $finalList = array();

            if ($view instanceof BView) {
                $rows = $view->rows;
                foreach ($rows as $row) {
                    if ($row->attributes['item_type'] == 'TreeItem') {
                        $parentId = $row->attributes['parent_id'];
                        $list = $this->getListById($parentId);
                        $finalList[$parentId] = $list;
                    }

                    if ($row->attributes['item_type'] == 'TreeList') {
                        $listId = $row->getId();
                        $list = $this->getListById($listId);
                        $finalList[$listId] = $list;
                    }
                }
            }

            return $finalList;
        }
    }

    public function getViewByFindText($findText = NULL, $findContext = NULL, $isLimited = TRUE) {
        if ($findContext == NULL)
            $findContext = array();

        if (!isset($findContext['itemMatch']))
            $findContext['itemMatch'] = '=';

        //first parse findText
        $itemData = explode(' ', $findText);

        if (isset($findContext['itemData']))
            $itemContext = $findContext['itemData'];
        else
            $itemContext = array();

        foreach ($itemData as $value) {
            if ($value != NULL) {
                $textList = explode('<', $value);
                $count = count($textList);
                if ($count == 2) {
                    $field = $textList[0];
                    $field1 = $field . '&';
                    $value = '<' . $textList[1];
                    if (isset($itemContext[$field]) || isset($itemContext[$field1])) {
                        $oldValue = isset($itemContext[$field]) ? $itemContext[$field] : $itemContext[$field1];
                        if (is_array($oldValue)) {
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (is_string($oldValue)) {
                            $oldValue = array($oldValue);
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (isset($itemContext[$field])) {
                            unset($itemContext[$field]);
                        }
                    } else {//it is new field
                        $itemContext[$field] = $value;
                    }

                    continue;
                }

                $textList = explode('>', $value);
                $count = count($textList);
                if ($count == 2) {
                    $field = $textList[0];
                    $field1 = $field . '&';
                    $value = '>' . $textList[1];
                    if (isset($itemContext[$field]) || isset($itemContext[$field1])) {
                        $oldValue = isset($itemContext[$field]) ? $itemContext[$field] : $itemContext[$field1];
                        if (is_array($oldValue)) {
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (is_string($oldValue)) {
                            $oldValue = array($oldValue);
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (isset($itemContext[$field])) {
                            unset($itemContext[$field]);
                        }
                    } else {//it is new field
                        $itemContext[$field] = $value;
                    }

                    continue;
                }

                $textList = explode('=', $value);
                $count = count($textList);
                if ($count == 2) {
                    $field = $textList[0];
                    $field1 = $field . '&';
                    $value = $textList[1];
                    if (isset($itemContext[$field]) || isset($itemContext[$field1])) {
                        $oldValue = isset($itemContext[$field]) ? $itemContext[$field] : $itemContext[$field1];
                        if (is_array($oldValue)) {
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (is_string($oldValue)) {
                            $oldValue = array($oldValue);
                            array_push($oldValue, $value);
                            $itemContext[$field1] = $oldValue;
                        }
                        if (isset($itemContext[$field])) {
                            unset($itemContext[$field]);
                        }
                    } else {//it is new field
                        $itemContext[$field] = $value;
                    }

                    continue;
                }
                if ($count == 1 && $value != NULL) {
                    array_push($itemContext, $value);
                }
            }
        }

        $findContext['itemData'] = $itemContext;

        $condition = $findContext;

        if (is_array($condition)) {
            if (!isset($condition['itemType']))
                $condition['itemType'] = 'TreeList';
        }

        $limitStart = 0;
        if (isset($condition['limitStart']))
            $limitStart = $condition['limitStart'];

        $view = $this->getView($condition, $isLimited);
        return $view;
    }

    public function getCount($condition = array()) {
        $tableNode = $this->getTableNode();

        if (is_array($condition)) {
            $condition['parentId'] = $tableNode->getId();
        }

        $sql = $this->populateFindCountSql($condition);

        $count = $tableNode->getRepository()->getCount(array('sql' => $sql));

        return $count;
    }

    public function getListCount($conditon = array()) {

        $count = NULL;

        $tableNode = $this->getTableNode();

        if ($tableNode instanceof BTreeItem) {
            // $findContext = $tableNode->getRepository()->populateFindContext($tableNode);
            $findContext = $this->treeItem->getRootNodeContext();
            if (is_array($findContext)) {
                $findContext['parentId'] = $tableNode->getId();
            }

            if ($this->ownerType != NULL) {
                $findContext['ownerType'] = $this->ownerType;
            }

            if ($this->ownerId != NULL) {
                $findContext['ownerId'] = $this->ownerId;
            }
            $count = $tableNode->getRepository()->getCount($findContext);
        }

        return $count;
    }

    public function getAggerateLists($condition = array()) {

        $aTableNode = $this->getAggerateTableNode();
        $finalList = array();

        if ($aTableNode instanceof BTreeItem) {
            $aNodes = $aTableNode->getChildren();
            foreach ($aNodes as $id => $aNode) {
                $list = new BTreeList($aNode);
                $finalList[$id] = $list;
            }
        }

        return $finalList;
    }

    /**
     * 
     * @param type $id
     * @return \BTreeList
     */
    public function getListById($id) {

        $class = $this->treeItemClass;

        $listNode = new $class($id);

        return new BTreeList($listNode);
    }

    /**
     * 
     * @param type $name
     * @return BTreeList
     */
    public function getListByName($name) {
        $context = array('name' => $name);
        return $this->getList($context);
    }

    public function getValueList($name, $findContext = NULL, $findText = NULL, $groupBy = TRUE, $returnCount = TRUE) {

        $view = array();

        if ($findText != NULL) {
            if ($groupBy == TRUE) {
                $groupBy = ' groupBy=' . $name;
                $findItems = explode(' ', $findText);
                $findText = '';
                foreach ($findItems as $key => $value) {
                    $items = explode(' ', $value);
                    if (isset($items['groupBy'])) {
                        $groupBy .= ',' . $items['groupBy'];
                    } else {
                        $findText .= $value . ' ';
                    }
                }

                $findText .= ' ' . $groupBy;
            }
            $view = $this->getViewByFindText($findText, $findContext, FALSE);
        } else {
            if ($groupBy) {
                $itemData = isset($findContext['itemData']) ? $findContext['itemData'] : array();
                if (isset($itemData['groupBy'])) {
                    $itemData['groupBy'].=',' . $name;
                } else {
                    $itemData['groupBy'] = $name;
                }
                $findContext['itemData'] = $itemData;
            }
            $view = $this->getView($findContext, FALSE);
        }

        $list = array();
        foreach ($view as $tlist) {
            $item = $tlist->getItem($name);
            if ($item instanceof BTreeItem) {
                if ($returnCount) {
                    $list[$item->getValue()] = $tlist->groupCount;
                } else {
                    $list[$item->getId()] = $item->getValue();
                }
            }
        }
        return $list;
    }

    public function getList($context = array()) {
        $tableNode = $this->getTableNode();
        if ($tableNode instanceof BTreeItem) {
            /*
              $rootContext = $tableNode->getRootNodeContext();
              if (isset($rootContext['name']))
              unset($rootContext['name']);
              $listContext = $rootContext;
              $listContext['itemType'] = 'TreeList';
              $itemContext = array();
             * 
             */
            if (is_array($context)) {

                $context['parentId'] = $tableNode->getId();

                if ($this->ownerType != NULL) {
                    $context['ownerType'] = $this->ownerType;
                }

                if ($this->ownerId != NULL) {
                    $context['ownerId'] = $this->ownerId;
                }

                if (isset($context['owner'])) {
                    $owner = $context['owner'];
                    if ($owner instanceof BEntity) {
                        $context['ownerType'] = get_class($owner);
                        if ($owner->getId() != NULL) {
                            $context['ownerId'] = $owner->getId();
                        }
                    }
                }

                $finalSql = $this->populateFindListSql($context);

                $view = $tableNode->getRepository()->getView(array('sql' => $finalSql));

                if ($view instanceof BView) {
                    $rows = $view->rows;
                    foreach ($rows as $row) {
                        $listId = $row->attributes['_id'];
                        return $this->getListById($listId);
                    }
                }

                /*

                  $listNode = $tableNode->getChild($context);

                  if ($listNode instanceof BTreeItem) {
                  $list = new BTreeList($listNode);
                  return $list;
                  }
                 * 
                 */
            }
        }
        return FALSE;
    }

    public function getListByOwner($owner) {
        $context = array('owner' => $owner);
        return $this->getList($context);
    }

    /**
     * 
     * @param type $data
     * @return BTreeList
     */
    public function getNewList($data = array(), $name = NULL) {
        if (is_string($data)) {
            $name = $data;
            $data = NULL;
        }
        return $this->addList($data, $name);
    }

    /**
     * this function will generate a temporary list according space and user
     * @param type $name
     */
    public function getTempList($name = NULL) {
        $tableName = $this->getTableName();
        $tempId = 'S001' . Blyn::app()->getActiveSpace()->getId() . 'U001' . Blyn::app()->getCurrentUser()->getId() . $tableName;
        $list = $this->getListByName($tempId);
        if ($list == NULL) {
            $list = $this->getNewList(NULL, $tempId);
        }
        return $list;
    }

    public function setOwner($owner) {
        if ($owner instanceof BEntity) {
            $this->ownerType = get_class($owner);
            $this->ownerId = $owner->getId();
        } else {
            $this->owner = NULL;
            $this->ownerType = NULL;
            $this->ownerId = NULL;
        }
    }

    public function getOwner($data = NULL) {
        return $this->owner;
    }

    public function deleteList($listId) {
        $list = $this->getListById($listId);
        $list->getListNode()->status = -1;
        $list->save();
    }

    public function getTableNode($data = NULL) {
        if ($this->tableNode == NULL) {
            $myRoot = $this->treeItem->getRootNode();

            //$tableNodeContext = $this->treeItem->getRootNodeContext();
            //$tableNodeContext['name'] = 'table.' . $this->tableName;

            $tableNode = $myRoot->getChild('table.' . $this->tableName);

            if (!$tableNode instanceof BTreeEntity || $tableNode->getId() == NULL) {
                $tableNode = new BTreeItem();
                $tableNode->name = 'table.' . $this->tableName;
                $tableNode->itemType = 'TreeTable';
                //$tableNodeContext['itemType'] = 'TreeTable';
                $tableNode = $myRoot->addChild($tableNode);
                //$tableNode = $myRoot->addChild(array('name' => 'table.' . $this->tableName, 'itemType' => 'TreeTable'));
            }
            $this->tableNode = $tableNode;
        }

        return $this->tableNode;
    }

    public function getTableName($data = NULL) {
        return $this->tableName;
    }

    public function getAggerateTableNode($data = NULL) {
        if ($this->aggerateTable == NULL) {
            $myRoot = $this->treeItem->getRootNode();

            $tableNode = $myRoot->getChild('aggerateTable');

            if (!$tableNode instanceof BTreeEntity || $tableNode->getId() == NULL) {
                $tableNode = $myRoot->addChild(array('name' => 'aggerateTable', 'itemType' => 'TreeTable'));
            }
            $this->tableNode = $tableNode;
        }

        return $this->tableNode;
    }

    protected function populateFindListSql($context = NULL, $isLimited = TRUE) {

        $tableNode = $this->getTableNode();
        if ($tableNode instanceof BTreeItem) {
            $rootContext = $tableNode->getRootNodeContext();
            if (isset($rootContext['name']))
                unset($rootContext['name']);
            $listContext = $rootContext;
            $listContext['itemType'] = 'TreeList';
            $itemContext = array();
            $limit = 50;
            $limitStart = 0;

            if (is_array($context)) {

                if (isset($context['groupBy'])) {
                    $itemGroupBy = $context['groupBy'];
                    unset($context['groupBy']);
                }

                $listContext += $context;
                if (isset($context['itemData'])) {
                    $itemContext = $context['itemData'];
                    unset($listContext['itemData']);
                    if (is_array($itemContext)) {
                        //$itemContext['item_type'] = 'TreeItem';
                        //if (!isset($itemContext['bookingStatus']))
                        //$itemContext['bookingStatus'] = 'available';
                    }

                    if (isset($itemContext['groupBy'])) {
                        $itemGroupBy = $itemContext['groupBy'];
                        unset($itemContext['groupBy']);
                    }


                    if (isset($itemContext['select'])) {
                        $itemSelect = $itemContext['select'];
                        unset($itemContext['select']);
                    }
                }
            }

            if (isset($context['limit'])) {
                $limit = $context['limit'];
            }
            if (isset($context['limitStart'])) {
                $limitStart = $context['limitStart'];
            }

            if (isset($context['orderBy'])) {
                $itemOrderBy = $context['orderBy'];
                unset($context['orderBy']);
            }

            if (isset($context['summary'])) {
                $itemSummary = $context['summary'];
                unset($context['summary']);
            }

            $itemMatch = '=';
            if (isset($context['itemMatch']))
                $itemMatch = $context['itemMatch'];

            if (!empty($listContext))
                $listContext = $tableNode->getRepository()->populateAttributeList($listContext);

            $condition = '';
            $params = array();

            $tableName = $tableNode->getRepository()->getTableName();

            $sql = "select t1._id,count(distinct t1._id) as count ";
            $tableItems = array('t1' => 't1');
            $tableSql = ' from ' . $tableName . 't1 ';
            $condition = ' where t1.status >= 0 ';
            $groupBy = ' group by t1._id ';

            if ($listContext != NULL) {
                foreach ($listContext as $name => $value) {
                    if ($condition == ' where ')
                        $condition .= ' t1.' . $name . " = '" . $value . "'";
                    else {
                        $condition .= ' and t1.' . $name . " ='" . $value . "'";
                    }
                }
            }

            if (isset($itemSelect)) {

                $items = preg_split('/[, ]/', $itemSelect);

                $i = 2;
                $selectItems = array();
                foreach ($items as $name) {
                    $selectItems[$name] = $name;
                }
            }

            if (isset($itemGroupBy)) {

                $items = preg_split('/[, ]/', $itemGroupBy);

                if (!isset($selectItems))
                    $selectItems = array();

                $i = 2;
                $groupItems = array();
                foreach ($items as $name) {
                    $groupItems[$name] = $name;
                    $selectItems[$name] = $name;
                }
            }

            if (isset($itemOrderBy) && $itemOrderBy != NULL) {

                $orderData = explode(' ', $itemOrderBy);
                $orderItems = array();
                //if (!isset($selectItems))
                //$selectItems = array();
                if (isset($orderData[0])) {
                    $orderItemString = $orderData[0];
                    $items = explode(',', $orderItemString);
                    foreach ($items as $name) {
                        $orderItems[$name] = $name;
                        //$selectItems[$name] = $name;
                    }
                }

                if (isset($orderData[1])) {
                    $sort = $orderData[1];
                }
            }

            if (isset($itemSummary) && $itemSummary != NULL) {
                $summaryItems = array();
                $summaryItems = $itemSummary;
            }

            $i = 2;
            if ($itemContext != NULL) {
                foreach ($itemContext as $key => $value) {
                    if ($value === NULL || $value == '')
                        continue;

                    $tableItems[$key] = 't' . $i;
                    //$sql .= "," . $tableName . " t" . $i;
                    if (is_string($key)) {
                        if (is_array($value)) {
                            if ($key == 'and' || $key == 'or') {
                                $operator = $key;
                                $condition.='(';
                                foreach ($value as $aKey => $aValue) {
                                    if (trim($condition) == 'where')
                                        $condition .= '(' . $this->populateConditionStringValue($aValue, $i, $aKey, $itemMatch) . ')';
                                    else
                                        $condition .= ' ' . $operator . ' (' . $this->populateConditionStringValue($aValue, $i, $aKey, $itemMatch) . ')';
                                }
                                $condition.=')';
                            } else {
                                $operator = 'and';
                                if (strpos($key, '&') === FALSE) {
                                    $operator = 'or';
                                } else {
                                    $tempIndex = $tableItems[$key];
                                    unset($tableItems[$key]);
                                    $key = str_replace('&', '', $key);
                                    $tableItems[$key] = $tempIndex;
                                }

                                if (count($value) == 1) {
                                    foreach ($value as $arrid => $arrvalue)
                                        $value0 = $arrvalue;
                                    $value = $value0;
                                }

                                //if have more value, populate values as or condition
                                if (count($value) > 1) {
                                    if (trim($condition) == 'where')
                                        $condition .= '(';
                                    else {
                                        $condition .= ' and (';
                                    }
                                    $j = 0;
                                    foreach ($value as $aValue) {
                                        $j++;
                                        if ($j == 1) {
                                            $condition .= $this->populateConditionStringValue($aValue, $i, $key, $itemMatch);
                                        }
                                        if ($j > 1) {
                                            $condition .= ' ' . $operator . $this->populateConditionStringValue($aValue, $i, $key, $itemMatch);
                                        }
                                        //$condition .= " or (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value = '" . $value . "' and t" . $i . '.parent_id= t1._id)';
                                    }
                                    $condition.=')';
                                }
                            }
                        }
                        if (is_string($value) || is_numeric($value) || $value == NULL) {
                            if (trim($condition) == 'where')
                                $condition .= $this->populateConditionStringValue($value, $i, $key, $itemMatch);
                            else
                                $condition .= ' and ' . $this->populateConditionStringValue($value, $i, $key, $itemMatch);
                        }
                        /*
                          if (is_string($value)) {
                          if (preg_match('/^[<>]/', $value)) {
                          $key = preg_replace('/[<>]$/', '', $key);
                          if ($condition == ' where ') {
                          $condition .= " t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value '" . $value . "' and t" . $i . '.parent_id= t1._id';
                          } else {
                          $condition .= " and t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value " . $value . " and t" . $i . '.parent_id= t1._id';
                          }
                          } else {
                          if ($condition == ' where ') {
                          if ($itemMatch == '=') {
                          $condition .= " t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                          }
                          if ($itemMatch == 'like') {
                          $condition .= " t" . $i . ".name like '" . $key . "%' and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                          }
                          } else {
                          if ($itemMatch == '=') {
                          $condition .= " and t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                          }
                          if ($itemMatch == 'like') {
                          $condition .= " and t" . $i . ".name like '" . $key . "%' and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                          }
                          }
                          }
                          }
                         * 
                         */
                    } else {
                        if ($condition == ' where ') {
                            if ($itemMatch == '=') {
                                $condition .= ' t' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                            }
                            if ($itemMatch == 'like') {
                                $condition .= ' t' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                            }
                        } else {
                            if ($itemMatch == '=') {
                                $condition .= " and " . 't' . $i . ".value= '" . $value . "%' and t" . $i . '.parent_id= t1._id';
                            }
                            if ($itemMatch == 'like') {
                                $condition .= " and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                            }
                        }
                    }
                    $i++;
                }
            }

            if (isset($groupItems) && $groupItems != NULL) {
                $groupBy = NULL;
                foreach ($groupItems as $value) {
                    if (isset($tableItems[$value]))
                        $tIndex = $tableItems[$value];
                    else {
                        $tIndex = 't' . $i++;
                        $tableItems[$value] = $tIndex;
                        $condition .= ' and ' . $tIndex . ".name='" . $value . "' and " . $tIndex . '.parent_id=t1._id';
                        $i++;
                    }
                    if ($groupBy == NULL) {
                        $groupBy = ' group by ' . $tIndex . '.value';
                        //$sql = 'select count(t1._id),' . $value;
                    } else {
                        $groupBy .= ',' . $tIndex . '.value';
                        //$sql .= ',' . $value;
                    }
                }
            }

            if (isset($orderItems) && $orderItems != NULL) {
                $orderBy = NULL;
                foreach ($orderItems as $value) {
                    if (isset($tableItems[$value]))
                        $tIndex = $tableItems[$value];
                    else {
                        $tIndex = 't' . $i++;
                        $tableItems[$value] = $tIndex;
                        $condition .= ' and ' . $tIndex . ".name='" . $value . "' and " . $tIndex . '.parent_id=t1._id';
                        $i++;
                    }
                    if ($orderBy == NULL) {
                        $orderBy = ' order by ' . $tIndex . '.value';
                    } else {
                        $orderBy .= ',' . $tIndex . '.value';
                    }
                }
            }

            if (isset($summaryItems) && $summaryItems != NULL) {
                foreach ($summaryItems as $key => $value) {
                    if (isset($tableItems[$value]))
                        $tIndex = $tableItems[$value];
                    else {
                        $tIndex = 't' . $i++;
                        $tableItems[$value] = $tIndex;
                        $condition .= ' and ' . $tIndex . ".name='" . $value . "' and " . $tIndex . '.parent_id=t1._id';
                        $i++;
                    }
                    $summaryString = ' sum(' . $tIndex . '.value) as ' . $key;
                }
            }

            if (isset($selectItems) && $selectItems != NULL) {
                $sql = NULL;
                foreach ($selectItems as $value) {
                    if ($sql == NULL) {
                        if (isset($tableItems[$value]))
                            $tIndex = $tableItems[$value];
                        else {
                            $tIndex = 't' . $i++;
                            $tableItems[$value] = $tIndex;
                            $i++;
                        }
                        $sql = 'select t1._id,count(distinct t1._id) as count,' . $tIndex . '.value';
                    } else {
                        $sql .= ',' . $value;
                    }
                }
            }

            if (is_array($tableItems) && $tableItems != NULL) {
                $tableSql = NULL;
                foreach ($tableItems as $value) {
                    if ($tableSql == NULL)
                        $tableSql = ' from ' . $tableName . ' ' . $value;
                    else {
                        $tableSql .= ',' . $tableName . ' ' . $value;
                    }
                }
            }

            if (isset($summaryString) && $summaryString != NULL) {
                $sql .=',' . $summaryString;
            }

            $finalSql = $sql . $tableSql . $condition . $groupBy;

            if (isset($orderBy) && $orderBy != NULL) {
                $finalSql .= $orderBy;

                if (isset($sort) && $sort != NULL) {
                    $finalSql.= ' ' . $sort;
                }
            }

            if ($isLimited)
                $finalSql .= ' limit ' . $limitStart . ',' . $limit;



            return $finalSql;
        }
    }

    protected function populateFindCountSql($context = NULL) {

        $tableNode = $this->getTableNode();
        if ($tableNode instanceof BTreeItem) {
            $rootContext = $tableNode->getRootNodeContext();
            if (isset($rootContext['name']))
                unset($rootContext['name']);
            $listContext = $rootContext;
            $listContext['itemType'] = 'TreeList';
            $itemContext = array();


            if (is_array($context)) {
                $listContext += $context;
                if (isset($context['itemData'])) {
                    $itemContext = $context['itemData'];
                    unset($listContext['itemData']);
                    if (is_array($itemContext)) {
                        //$itemContext['item_type'] = 'TreeItem';
                        //if (!isset($itemContext['bookingStatus']))
                        //$itemContext['bookingStatus'] = 'available';
                    }
                }

                $itemMatch = '=';
                if (isset($context['itemMatch']))
                    $itemMatch = $context['itemMatch'];

                if (!empty($listContext))
                    $listContext = $tableNode->getRepository()->populateAttributeList($listContext);

                $condition = '';
                $params = array();

                $tableName = $tableNode->getRepository()->getTableName();

                $sql = "select count(DISTINCT t1._id) from " . $tableName . " t1";
                $condition = ' where t1.status >= 0 ';
                $groupBy = ' group by t1._id ';

                if ($listContext != NULL) {
                    foreach ($listContext as $name => $value) {
                        if ($condition == ' where ')
                            $condition .= ' t1.' . $name . " = '" . $value . "'";
                        else {
                            $condition .= ' and t1.' . $name . " ='" . $value . "'";
                        }
                    }
                }

                if ($itemContext != NULL) {
                    $i = 2;
                    foreach ($itemContext as $key => $value) {
                        if ($value === NULL || $value == '')
                            continue;
                        $sql .= "," . $tableName . " t" . $i;
                        if (is_string($key)) {
                            if (is_array($value)) {
                                if ($key == 'and' || $key == 'or') {
                                    $operator = $key;
                                    $condition.='(';
                                    foreach ($value as $aKey => $aValue) {
                                        if (trim($condition) == 'where')
                                            $condition .= '(' . $this->populateConditionStringValue($aValue, $i, $aKey, $itemMatch) . ')';
                                        else
                                            $condition .= ' ' . $operator . ' (' . $this->populateConditionStringValue($aValue, $i, $aKey, $itemMatch) . ')';
                                    }
                                    $condition.=')';
                                }else {
                                    $operator = 'and';
                                    if (strpos($key, '&') === FALSE) {
                                        $operator = 'or';
                                    } else {
                                        $tempIndex = $tableItems[$key];
                                        unset($tableItems[$key]);
                                        $key = str_replace('&', '', $key);
                                        $tableItems[$key] = $tempIndex;
                                    }

                                    if (count($value) == 1) {
                                        $value = $value[0];
                                    }

                                    //if have more value, populate values as or condition
                                    if (count($value) > 1) {
                                        if (trim($condition) == 'where')
                                            $condition .= '(';
                                        else
                                            $condition .= ' and (';
                                        $j = 0;
                                        foreach ($value as $aValue) {
                                            $j++;
                                            if ($j == 1) {
                                                $condition .= '(' . $this->populateConditionStringValue($aValue, $i, $key, $itemMatch) . ')';
                                            }
                                            if ($j > 1) {
                                                $condition .= ' ' . $operator . ' (' . $this->populateConditionStringValue($aValue, $i, $key, $itemMatch) . ' )';
                                            }
                                            //$condition .= " or (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value = '" . $value . "' and t" . $i . '.parent_id= t1._id)';
                                        }
                                        $condition.=')';
                                    }
                                }
                            }

                            if (is_string($value) || is_numeric($value) || $value == NULL) {
                                if (trim($condition) == 'where')
                                    $condition .= $this->populateConditionStringValue($value, $i, $key, $itemMatch);
                                else
                                    $condition .= ' and ' . $this->populateConditionStringValue($value, $i, $key, $itemMatch);
                            }
                            /*
                              if (strpos($value, 'or') !== FALSE) {
                              $value = trim(str_replace('or ', '', $value));
                              $names = explode('_', $key);
                              $key = $names[0];
                              $condition .= " or (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value = '" . $value . "' and t" . $i . '.parent_id= t1._id)';
                              } else if (preg_match('/^[<>]/', $value)) {
                              $key = preg_replace('/[<>]$/', '', $key);
                              if ($condition == ' where ') {
                              $condition .= " t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value '" . $value . "' and t" . $i . '.parent_id= t1._id';
                              } else {
                              $condition .= " and t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value " . $value . " and t" . $i . '.parent_id= t1._id';
                              }
                              } else {
                              if ($condition == ' where ') {
                              if ($itemMatch == '=') {
                              $condition .= " t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                              }
                              if ($itemMatch == 'like') {
                              $condition .= " t" . $i . ".name like '" . $key . "%' and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                              }
                              } else {
                              if ($itemMatch == '=') {
                              $condition .= " and t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                              }
                              if ($itemMatch == 'like') {
                              $condition .= " and t" . $i . ".name like '" . $key . "%' and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                              }
                              }
                              }
                             * 
                             */
                        } else {
                            if ($condition == ' where ') {
                                if ($itemMatch == '=') {
                                    $condition .= 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                                }
                                if ($itemMatch == 'like') {
                                    $condition .= 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                                }
                            } else {
                                if ($itemMatch == '=') {
                                    $condition .= " and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id';
                                }
                                if ($itemMatch == 'like') {
                                    $condition .= " and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id';
                                }
                            }
                        }
                        $i++;
                    }
                }

                $finalSql = $sql . $condition;

                return $finalSql;
            }
        }

        return FALSE;
    }

    protected function populateConditionStringValue($value, $tableIndex, $colName, $itemMatch, $condition = NULL, $operator = 'and') {
        if ($condition == NULL)
            $condition = '';

        $i = $tableIndex;
        $key = $colName;

        if (is_string($value) || is_numeric($value)) {
            $value = trim($value);
            if (preg_match('/^[<>]/', $value)) {
                $key = preg_replace('/[<>]$/', '', $key);
                $condition .= " (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value " . $value . " and t" . $i . '.parent_id= t1._id)';
            } else if (preg_match('/^(like)/', $value)) {
                $condition .= " (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value " . $value . " and t" . $i . '.parent_id= t1._id)';
            } else {
                if ($itemMatch == '=') {
                    $condition .= " (t" . $i . ".name = '" . $key . "' and " . 't' . $i . ".value= '" . $value . "' and t" . $i . '.parent_id= t1._id)';
                }
                if ($itemMatch == 'like') {
                    $condition .= " (t" . $i . ".name like '" . $key . "%' and " . 't' . $i . ".value like '%" . $value . "%' and t" . $i . '.parent_id= t1._id)';
                }
            }
        }
        if ($value == NULL) {//if value is null, match to all value
            $condition .= " (t" . $i . ".name = '" . $key . "' and t" . $i . '.parent_id= t1._id)';
        }
        return $condition;
    }

}
